package app.qrcode.browser.todoapp.data

import androidx.lifecycle.LiveData
import androidx.room.*
import app.qrcode.browser.todoapp.data.models.TaskStatus
import app.qrcode.browser.todoapp.data.models.ToDoData

/**
 * Created by YiGo on 2022/5/6.
 */
@Dao
interface ToDoDao {
    @Query("SELECT * FROM TODO_TABLE ORDER BY id ASC")
    fun getAllData(): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE WHERE priority = 'HIGH'")
    fun classifiedIntoHighPriority(): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE WHERE priority = 'MEDIUM'")
    fun classifiedIntoMediumPriority(): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE WHERE priority = 'LOW'")
    fun classifiedIntoLowPriority(): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE WHERE taskStatus = 'TODO'")
    fun classfiedIntoHighTaskStatus(): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE WHERE taskStatus = 'QUALIFY_INSTANCE'")
    fun classfiedIntoMediumTaskStatus(): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE WHERE taskStatus = 'DONE'")
    fun classfiedIntoLowTaskStatus(): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE WHERE isNoticed = '1'")
    fun chooseIsNoticedItem(): List<ToDoData>

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insertData(toDoData: ToDoData)

    @Update
    suspend fun updateData(toDoData: ToDoData)

    @Query("UPDATE TODO_TABLE SET taskStatus = :taskStatus where id = :id")
    suspend fun updateTaskStatus(id: Int, taskStatus: TaskStatus)

    @Delete
    suspend fun deleteItem(toDoData: ToDoData)

    @Query("DELETE FROM TODO_TABLE")
    suspend fun deleteAll()

    @Query("SELECT * FROM TODO_TABLE WHERE title like :searchQuery")
    fun searchDataBase(searchQuery: String): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE ORDER BY CASE WHEN priority LIKE 'H%' THEN 1 WHEN priority LIKE 'M%' THEN 2 WHEN priority LIKE 'L%' THEN 3 END")
    fun sortByHighPriority(): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE ORDER BY CASE WHEN priority LIKE 'L%' THEN 1 WHEN priority LIKE 'M%' THEN 2 WHEN priority LIKE 'H%' THEN 3 END")
    fun sortByLowPriority(): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE ORDER BY CASE WHEN taskStatus LIKE 'T%' THEN 1 WHEN taskStatus LIKE 'Q%' THEN 2 WHEN taskStatus LIKE 'D%' THEN 3 END")
    fun sortByHighTaskStatus(): LiveData<List<ToDoData>>

    @Query("SELECT * FROM TODO_TABLE ORDER BY CASE WHEN taskStatus LIKE 'D%' THEN 1 WHEN taskStatus LIKE 'Q%' THEN 2 WHEN taskStatus LIKE 'T%' THEN 3 END")
    fun sortByLowTaskStatus(): LiveData<List<ToDoData>>
}